package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.LinkedList;

import ControlLayer.PersonCtr;
import ModelLayer.Guest;
import ModelLayer.KeyWord;
import ModelLayer.Room;
import ModelLayer.RoomType;

public class DBRoom implements IFDBRoom {
	private Connection con;
	private PersonCtr personCtr;

	public DBRoom() {
		con = DBConnection.getInstance().getDBcon();
		personCtr = new PersonCtr();
	}

	@Override
	public Room getRoom(int roomId, boolean retrieveAssociation) {
		String wClause = "  roomId = '" + roomId + "'";
		return singleWhere(wClause, retrieveAssociation);
	}

	@Override
	public int insertRoom(Room room) {
		int nextId = GetMaxId.getMaxId("Select max(roomId) From Room") + 1;
		System.out.println("next roomId = " + nextId);
		String date = "";
		if (room.getEndDate() != null) {
			date = new SimpleDateFormat("dd/MM/yyyy").format(room.getEndDate());
		}
		String query = "INSERT INTO room(roomId, name, roomType_Id, status, checkOutDate) VALUES('"
				+ nextId
				+ "','"
				+ room.getName()
				+ "','"
				+ room.getRoomType().getId()
				+ "','"
				+ room.getStatus().toString() + "','" + date + "')";
		System.out.println("insert : " + query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			stmt.executeUpdate(query);
			stmt.close();
			con.commit();
		} catch (SQLException ex) {
			System.out.println("Room is not inserted");
		}
		return nextId;
	}

	@Override
	public LinkedList<Room> getAllRoom(boolean retrieveAssociation) {
		return miscWhere("", retrieveAssociation);
	}

	@Override
	public int updateRoom(Room room) {
		Room roomObj = room;
		int rc = -1;
		String date = "";
		if (roomObj.getEndDate() != null) {
			date = new SimpleDateFormat("dd/MM/yyyy").format(roomObj
					.getEndDate());
		}
		String query = "UPDATE room SET " + "name = '" + roomObj.getName()
				+ "'," + "roomType_Id = '" + roomObj.getRoomType().getId()
				+ "'," + "status = '" + roomObj.getStatus().toString() + "',"
				+ "checkOutDate = '" + date + "'" + " WHERE roomId = '"
				+ roomObj.getId() + "'";
		System.out.println("Update query:" + query);

		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		} catch (Exception e) {
			System.out.println("Update exception in room db: " + e);
		}
		return rc;
	}

	@Override
	public int deleteRoom(int roomId) {
		int rc = -1;

		String query = "DELETE FROM room WHERE roomId = '" + roomId + "'";
		System.out.println(query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		} catch (Exception ex) {
			System.out.println("Delete exception in room db: " + ex);
		}
		return rc;
	}

	@Override
	public LinkedList<Room> getAllRoomForRT(int rtId,
			boolean retrieveAssociation) {
		return miscWhere(" roomType_Id = '" + rtId + "'", retrieveAssociation);
	}

	private Room singleWhere(String wClause, boolean retrieveAssociation) {
		ResultSet results;

		Room room = new Room();
		String query = buildQuery(wClause);
		System.out.println(query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			if (results.next()) {
				room = buildRoom(results);
				stmt.close();
				if (retrieveAssociation) {
					IFDBRoomType dbRoomType = new DBRoomType();
					RoomType roomType = dbRoomType.getRoomType(room
							.getRoomType().getId());
					room.setRoomType(roomType);
					System.out.println("Roomtype is selected");

					ArrayList<Guest> guests = personCtr.getGuestForRoom(room
							.getId());
					room.setGuests(guests);
					System.out.println("Guests are selected");
				}
			} else {
				room = null;
			}
		} catch (Exception e) {
			System.out.println("Query exception: " + e);
		}
		return room;
	}

	private LinkedList<Room> miscWhere(String wClause,
			boolean retrieveAssociation) {
		ResultSet results;
		LinkedList<Room> list = new LinkedList<Room>();
		String query = buildQuery(wClause);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			while (results.next()) {
				Room room = new Room();
				room = buildRoom(results);
				list.add(room);
			}
			stmt.close();
			if (retrieveAssociation) {
				for (Room room : list) {
					IFDBRoomType dbRoomType = new DBRoomType();
					RoomType roomType = dbRoomType.getRoomType(room
							.getRoomType().getId());
					room.setRoomType(roomType);
					System.out.println("Roomtype is selected");

					ArrayList<Guest> guests = personCtr.getGuestForRoom(room
							.getId());
					room.setGuests(guests);
					System.out.println("Guests are selected");
				}
			}
		} catch (Exception e) {
			System.out.println("Query exception - select: " + e);
			e.printStackTrace();
		}
		return list;
	}

	private Room buildRoom(ResultSet results) {
		Room room = new Room();
		try {
			room.setId(results.getInt("roomId"));
			room.setName(results.getString("name"));
			room.setRoomType(new RoomType(results.getInt("roomType_Id")));
			room.setStatus(KeyWord.valueOf(results.getString("status")));
			if (!results.getString("checkOutDate").isEmpty())
				room.setEndDate(new SimpleDateFormat("dd/MM/yyyy")
						.parse(results.getString("checkOutDate")));
		} catch (Exception e) {
			System.out.println("error in building the room object");
		}
		return room;
	}

	private String buildQuery(String wClause) {
		String query = "SELECT roomId, name, roomType_Id, status, checkOutDate FROM room";
		if (wClause.length() > 0) {
			query = query + " WHERE" + wClause;
		}
		return query;
	}

}
